package classes
{
	import flash.data.SQLColumnSchema;
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLSchemaResult;
	import flash.data.SQLStatement;
	import flash.data.SQLTableSchema;
	import flash.filesystem.File;

	public class DatabaseManager
	{
		private static var _singleton:DatabaseManager;
		public static var fileDB:File = null;
		 
		private var _sqlConn:SQLConnection;
		private var _database_filename:String;
		
		public function DatabaseManager(database:String = "")
		{
			this._database_filename = database;
			var databaseFile:File;
			if ( fileDB == null ) 
				databaseFile = File.applicationDirectory.resolvePath(this._database_filename);
			else
				databaseFile = fileDB;
				
			this._sqlConn = new SQLConnection();
			this._sqlConn.open(databaseFile);
			try{
				this._sqlConn.loadSchema();
			}catch(err:Error){
				
			}
			this.InitSchema();
		}
		
		public static function get Manager():DatabaseManager{
			try{
				if ( DatabaseManager._singleton == null ){
					DatabaseManager._singleton = new DatabaseManager("Trends.db");
				}
			}catch(err:Error){
				throw err;
			}
			return DatabaseManager._singleton; 
		}

		protected function ProcessQuery(query:String):Array{
			try{
				var stm:SQLStatement = new SQLStatement();
				stm.text = query;
				stm.sqlConnection = this._sqlConn;
				stm.execute();
				var sqlRes:SQLResult = stm.getResult();
				if ( sqlRes == null || sqlRes.data == null) return [];
				else return sqlRes.data;
			}catch(err:Error){
				throw err;
			}
			return [];
		}
				
		private function CreateTable(tableName:String,tableFields:Array):void{
			try{
				if ( tableName == "" || tableFields.length == 0 ){
					return;
				}
				var sch:SQLSchemaResult = this._sqlConn.getSchemaResult();
				var tbl:SQLTableSchema = null;

				if ( sch != null ){
					for each (var table:SQLTableSchema in sch.tables){
						if (table.name == tableName){
					  		tbl = table;
					  		break;
						}
					}
				}
				
				if ( tbl == null ){
					this.ProcessQuery( "CREATE TABLE IF NOT EXISTS " + tableName + "(" + tableFields.join(",") + ")");
					this._sqlConn.loadSchema();
				}else{
					try{
						this.Begin();
						
						for each ( var addF:String in tableFields ){
							var col_toks:Array = addF.split(" ");
							var found:Boolean = false;
							for each ( var colS:SQLColumnSchema in tbl.columns ){
								if ( colS.name == col_toks[0] ){
									found = true;
									break;
								}
							}
							if ( !found )
								this.ProcessQuery("ALTER TABLE "+tableName+" ADD "+ addF );
						} 
						this.Commit();
					}catch(err:Error){
						this.Rollback();
						throw err;
					}					
				}
			}catch(err:Error){
				throw err;
			}
		}

		public function Insert(tableName:String,nameValue:Object):void{
			try{
				var query:String = "INSERT INTO "+tableName;
				var names:String = "";
				var values:String = "";
				for ( var singleName:String in nameValue ){
					if ( names != "" ) names += ",";
					if ( values != "" ) values += ",";
					names += singleName;
					if ( nameValue[singleName] is String ){
						var value_in_string:String = nameValue[singleName];
						value_in_string = value_in_string.split("'").join("''");
						values += "'" + value_in_string + "'";
					}else{
						values += nameValue[singleName];
					}
				}
				query += "("+names+") VALUES ("+values+")";
				//Alert.show(query,"Insert Query");
				this.ProcessQuery(query);
			}catch(err:Error){
				throw err;
			}
		}
		
		public function Select(tableName:String, valueFields:Array = null,
		condition:String = "", orderby:String = "",inXmlList:Boolean = false,xmlNodeName:String = ""):Array{
			try{
				var query:String = "SELECT ";
				if ( valueFields == null ){
					query += "*";
				}else{
					query += valueFields.join(",");
				}
				query += " FROM " + tableName;
				if ( condition != "" )query += " WHERE " + condition;
				if ( orderby != "" ) query += " ORDER BY " + orderby;
				
				var retValue:Array = this.ProcessQuery(query);
				if ( ! inXmlList ) return retValue;
				
				var retXml:XML = new XML(<retValue />);
				for each ( var singleObj:Object in retValue ){
					var attr:String;
					var xml:XML = new XML("<"+xmlNodeName+" />");
					
					for ( attr in singleObj ){
						xml.@[attr] = singleObj[attr];
					}
					retXml.appendChild(xml);
				}
				
				return retXml.children();
			}catch(err:Error){
				throw err;
			}
			return [];
		}

		public function Update ( tableName:String , nameValue:Object , condition:String = "" ):void{
			try{
				var query:String = "";
				for ( var singleName:String in nameValue ){
					if ( query != "" ) query += ", ";
					if ( nameValue[singleName] is String ) query += singleName + "= '" + nameValue[singleName]+"'";
					else query += singleName + "=" + nameValue[singleName];
				}
				query = "UPDATE " + tableName + " SET " + query;
				if ( condition != "" ) query += " WHERE " + condition;
				
				this.ProcessQuery(query);
			}catch(err:Error){
				throw err;
			}
		}
		
		public function Delete(tableName:String, condition:String = "" ):void{
			try{
				var query:String = "DELETE FROM "+tableName;
				if ( condition != "" ) query += " WHERE "+condition;
				
				this.ProcessQuery(query); 
			}catch(err:Error){
				throw err;
			}
		}
		
		public function Begin():void{
			try{
				this._sqlConn.begin();
			}catch(err:Error){
				throw err;
			}
		}
		
		public function get LastInsertRowID():Number{
			try{
				return this._sqlConn.lastInsertRowID;
			}catch(err:Error){
				throw err;
			}
			return 0;
		}

		public function Commit():void{
			try{
				this._sqlConn.commit();
				this._sqlConn.loadSchema();				
			}catch(err:Error){
				throw err;
			}
		}

		public function Rollback():void{
			try{
				this._sqlConn.rollback();
			}catch(err:Error){
				throw err;
			}
		}
		
		private function InitSchema():void{
			try{
				this.CreateTable("areas",["areaname TEXT PRIMARY KEY","lng TEXT","lat TEXT"]);
				this.CreateTable("appusers",["userid TEXT PRIMARY KEY", "password TEXT","name TEXT","gender TEXT" , "dob TEXT" , "city TEXT" , "country TEXT"]);
				this.CreateTable("trends",["trendsid INTEGER PRIMARY KEY AUTOINCREMENT","resturantid TEXT","resturantname TEXT","userid TEXT","checkintime TEXT"]);
				this.InitAppUsers();
				this.InitAreas();
			}catch(err:Error){
				throw err;
			}
		}
		
		private function InitAppUsers():void{
			try{
				var haveDefaultUser:Boolean = false;
				
				for each ( var singleObj:Object in this.Select("appusers") ){
					if ( singleObj.userid == "admin" ){
						haveDefaultUser = true;
					}
				}
				
				if ( !haveDefaultUser ){
					var insertObj:Object = new Object ;
					insertObj.userid = "admin";
					insertObj.password = "admin123";
					insertObj.name="admin";
					insertObj.gender="MALE";
					insertObj.dob="2000-12-31";
					insertObj.city="lahore";
					insertObj.country="pakistan";
					
					this.Insert("appusers",insertObj);
				}
			}catch(err:Error){
				throw err;
			}
		}
		
		private function InitAreas():void{
			try{
				var arrAreas:Array = [
				"MODEL TOWN",
				"DHA",
				"GULBERG",
				"JOHAR TOWN",
				"FAISAL TOWN"
				];
				
				var arrLat:Array = [
				"31.485722",
				"31.4738038",
				"31.5136607",
				"31.4522567",
				"31.4833333"
				];

				var arrLng:Array = [
				"74.3264869",
				"74.3770874",
				"74.3512799",
				"74.2810965",
				"74.3"
				];
				
				var list:Array = new Array();
				
				for each ( var itm:Object in this.Select("areas") ){
					list.push(itm.areaname);
				}
				
				this.Begin();
				
				var i:Number = 0 ;
				
				for ( i = 0 ; i < arrAreas.length ; i ++ ){
					var singleArea:String = arrAreas[i];
					
					if ( list.indexOf(singleArea) >= 0 ) continue;
					
					var insertObj:Object = new Object();
					insertObj.areaname = singleArea;
					insertObj.lat = arrLat[i];
					insertObj.lng = arrLng[i];
					this.Insert("areas",insertObj);
				}
				this.Commit();
			}catch(err:Error){
				throw err;
			}
		}
	}
}//
